/**
 * 
 */
package com.aaron.server;

import java.sql.*;


/**
 * @author aaron
 *
 */
public class SessionServer {
	private Connection con;
	public SessionServer()throws DatabaseException{
		try{
			String username="aaron";
			String password="li!insin";
			String url = "jdbc:mysql://localhost/VivCode";
			Class.forName ("com.mysql.jdbc.Driver").newInstance();
			con = DriverManager.getConnection (url, username, password);
		}
		catch(ClassNotFoundException e){
			System.err.println ("ClassNotFoundException "+e.getMessage());
			throw new DatabaseException(e.getMessage());
		}
		catch(InstantiationException e){
			System.err.println ("InstantiationException "+e.getMessage());
			throw new DatabaseException(e.getMessage());
		}
		catch(IllegalAccessException e){
			System.err.println ("IllegalAccessException "+e.getMessage());
			throw new DatabaseException(e.getMessage());
		}
		catch(SQLException e){
			System.err.println ("SQLException "+e.getMessage());
			throw new DatabaseException(e.getMessage());
		}
		System.out.println ("Database connection established");
	}
	public void login(String username, String auth){
		try{
			//check for any previous session
			String sql="SELECT COUNT(*) FROM `Sessions` WHERE `username` = '"+username+"' LIMIT 1;";
			Statement stmt = con.createStatement();
			//if previous session found, delete it
			ResultSet rs=stmt.executeQuery(sql);
			if(rs.next()){
				sql="DELETE FROM `Sessions` WHERE `username` = '"+username+"' LIMIT 1;";
				stmt.executeUpdate(sql);
			}
			//create new session
			sql="INSERT INTO `Sessions` ( `auth` , `username` , `last_update` ) VALUES ('"+auth+"', '"+username+"', CURRENT_TIMESTAMP());";
			stmt.executeUpdate(sql);
		}
		catch(SQLException e){
			System.err.println ("Login:SQLException "+e.getMessage());
		}
	}
	public boolean confirmAndSync(String auth){
		try{
			//find last_update time for given $key
			String sql="SELECT COUNT(*) FROM `Sessions` WHERE `auth` = '"+auth+"' AND TIMEDIFF(NOW(),`last_update`) <= '00:10:00' LIMIT 1;";
			Statement stmt = con.createStatement();
			ResultSet rs=stmt.executeQuery(sql);
			if(rs.next()){
				sql="UPDATE `Sessions` SET `last_update` = CURRENT_TIMESTAMP() WHERE `auth` = '"+auth+"' LIMIT 1;";
				System.err.println ("confirmAndSync: updated: "+auth);
				stmt.executeUpdate(sql);
				return true;					
			}
			//timeout
			else{
				System.err.println ("confirmAndSync: timeout: "+auth);
				this.logout(auth);
				return false;
			}
		}
		catch(SQLException e){
			System.err.println ("confirmAndSync: SQLException: "+e.getMessage());
			return false;
		}
	}
	public String getUsername(String auth){
		try{
			String sql="SELECT `username` FROM `Sessions` WHERE `auth` = '"+auth+"' LIMIT 1;";
			Statement stmt = con.createStatement();
			ResultSet rs=stmt.executeQuery(sql);
			if(rs.next()){
				return rs.getString("username");
			}
			else{
				System.err.println ("getUsername: could not find auth: "+auth);
				return "";
			}
		}
		catch(SQLException e){
			System.err.println ("getUsername: SQLException: "+e.getMessage());
			return "";
		}
	}
	public boolean logout(String auth){
		try{
			String sql="DELETE FROM `Sessions` WHERE `auth` = '"+auth+"' LIMIT 1;";
			Statement stmt = con.createStatement();
			int updated_rows=stmt.executeUpdate(sql);
			if(updated_rows>0){
				return true;
			}
			else{
				return false;
			}
		}
		catch(SQLException e){
			System.err.println ("logout: SQLException: "+e.getMessage());
			return false;
		}
	}
}
